﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class ApiVacationOptionRepository : BaseRepository<VacationOption>
    {
        protected override string TableName
        {
            get { return "[VacationOption]"; }
        }

        public override IEnumerable<VacationOption> FindAll()
        {
            string strSQL = @"  select vo.OptionId, vo.OptionName,
                    convert(bit, case 
                                    when exists(select 1 from PackageDestinationOption (nolock) as pdo where vo.OptionId = pdo.OptionId) then 0
                                else 1 end) as CanDelete
                    from [VacationOption] (nolock) as vo 
                    order by vo.OptionName ";

            IEnumerable<VacationOption> result = Connection.Query<VacationOption>(strSQL);
            return result;
        }

        public override VacationOption Find(int id)
        {
            VacationOption result = Connection.Query<VacationOption>(
                @"  select vo.OptionId, vo.OptionName
                    from [VacationOption] (nolock) as vo
                    where vo.OptionId = @OptionId", new { OptionId = id }).SingleOrDefault();
            return result;
        }

        public IEnumerable<VacationOption> FindNotInDest(int pkgDestId)
        {
            string strSQL = @"  select vo.OptionId, vo.OptionName,
                    convert(bit, case 
                                    when exists(select 1 from PackageDestinationOption (nolock) as pdo where vo.OptionId = pdo.OptionId) then 0
                                else 1 end) as CanDelete
                    from [VacationOption] (nolock) as vo
                    where not exists(select 1 from [PackageDestinationOption] (nolock) as pdo where pdo.PkgDestId = @PkgDestId and pdo.OptionId = vo.OptionId) 
                    order by vo.OptionName ";

            IEnumerable<VacationOption> result = Connection.Query<VacationOption>(strSQL, new { PkgDestId = pkgDestId });
            return result;
        }

        public override void Insert(VacationOption item)
        {
            Connection.Execute(
                @"insert into [VacationOption] 
                (OptionName)
                values (@OptionName)", item);
        }

        public override void Update(VacationOption item)
        {
            Connection.Execute(
                @"update VacationOption
                    set OptionName = @OptionName
                    where (OptionId = @OptionId)", item);
        }

        public override void Delete(int id)
        {
            Connection.Execute(
                @"delete 
                    from VacationOption
                    where (OptionId = @OptionId)", new { OptionId = id });
        }

    }
}
